use rusqlite::{Connection};
use crate::domin::Category;
use crate::data::db_path;



#[tauri::command]
pub fn category_update_sort(categorylist:Vec<Category>) -> usize {
    let mut conn = Connection::open(db_path()).expect("打开数据库失败");
    let tr = conn.transaction().expect("事务失败");
    categorylist.iter().for_each(|category| {
        tr.execute("update tb_category set sort = ?1 where id = ?2", (category.sort.clone(), category.id.clone())).expect("更新数据失败");
    });
    let _x = tr.commit().expect("事务提交失败");
    1
}

/// # category 表查询所有数据
#[tauri::command]
pub fn category_search () -> String{
    let db_path = db_path();
    let conn = Connection::open(db_path).expect("打开数据库失败");
    let mut stat = conn.prepare("select * from tb_category order by length(sort), sort asc").expect("创建statement失败");
    let categories = stat.query_map([],|row|{
        Ok(Category { sort: row.get(0)?, id: row.get(1)?, name: row.get(2)? })
    }).expect("执行sql语句出错");
    let mut vec = Vec::new();
    for category in categories {
        vec.push(category.expect("获取数据出错"))
    }
    serde_json::to_string(&vec).expect("序列化数据出错")
}

/// # category 表添加数据
#[tauri::command]
pub fn category_insert (category: Category ) -> usize{
    let conn = Connection::open(crate::data::db_path()).expect("打开数据库失败");
    let mut stat = conn.prepare("insert into tb_category (sort,id,name) values (?1,?2,?3);").expect("创建statement失败");
    stat.execute([category.sort.to_string(),category.id,category.name]).expect("执行sql语句出错")
}

/// # category 表 通过 ID 更新数据
#[tauri::command]
pub fn category_update (category: Category) -> usize{
    let conn = Connection::open(db_path()).expect("打开数据库失败");
    let mut stat = conn.prepare("update tb_category set sort=?, name = ? where id = ? ;").expect("创建statement失败");
    stat.execute([category.sort.to_string(),category.name,category.id]).expect("执行sql语句出错")
}

/// # category 表 通过 ID 删除数据
#[tauri::command]
pub fn category_delete (id : String) -> usize{
    let conn = Connection::open(db_path()).expect("打开数据库失败");
    let mut stat = conn.prepare("delete from tb_category where id = ?;").expect("创建statement失败");
    stat.execute([id]).expect("category_delete () : 执行sql语句出错")
}